﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.IO;
using System.Diagnostics;

namespace LicenseAutoDetection
{ 
    class ExcelSearch
    {
        // 확장명 XLS (Excel 97~2003 용)
        static readonly string ConnectStrFrm_Excel97_2003 =
           "Provider=Microsoft.Jet.OLEDB.4.0;" +
           "Data Source=\"{0}\";" +
           "Mode=ReadWrite|Share Deny None;" +
           "Extended Properties='Excel 8.0; HDR=NO; IMEX=1; READONLY=1';" +
           "Persist Security Info=False";

        // 확장명 XLSX (Excel 2007 이상용)
        static readonly string ConnectStrFrm_Excel =
           "Provider=Microsoft.ACE.OLEDB.12.0;" +
           "Data Source=\"{0}\";" +
           "Mode=ReadWrite|Share Deny None;" +
           "Extended Properties='Excel 12.0; HDR=NO; IMEX=1; READONLY=1';" +
           "Persist Security Info=False";

        static readonly string sqlStr = "SELECT * FROM [{0}]";
        static readonly string[] DefaultPattern = new string[] { "^\\d{6}-[1-6]\\d{6}$", "^0\\d{1,2}-\\d{3,4}-\\d{4}$",
                                                                 "^0\\d{8,10}$" };

        MainForm mainform;

        public ExcelSearch(MainForm form)
        {
            mainform = form;
        }
        //private int fileType(string path)
        //{
        //    byte[,] header = {
        //        { 0xD0, 0xCF, 0x11, 0xE0, 0xA1 }, // xls header
        //        { 0x50, 0x4B, 0x03, 0x04, 0x14 }  // xlsx header
        //    };

        //    short result = -1; // -2 = error, -1 = not excel, 0 = xls, 1 = xlsx

        //    FileInfo fileInfo = new FileInfo(path);
        //    FileStream fileStream = fileInfo.Open(FileMode.Open);

        //    try
        //    {
        //        byte[] fileHeader = new byte[5];

        //        fileStream.Read(fileHeader, 0, 5);

        //        for (short h = 0; h < 2; h++)
        //        {
        //            for (short element = 0; element < 5; element++)
        //            {
        //                if (fileHeader[element] != header[h, element]) break;
        //                else if (element == 4) result = h;
        //            }
        //            if (result != -1) break;
        //        }
        //    }
        //    catch (System.Exception ex)
        //    {
        //        Console.WriteLine(ex.Message);
        //        result = -2;
        //    }
        //    finally
        //    {
        //        fileStream.Close();
        //        fileStream.Dispose();
        //    }

        //    return result;
        //}

        private bool tableSearching(OleDbConnection dbConn)
        {
            bool complete = false;
            using (DataTable schema = dbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" }))
            {
                foreach (DataRow DR in schema.Rows)
                {
                    string tableName = DR["TABLE_NAME"].ToString();
                    if (tableName.EndsWith("'") && tableName.StartsWith("'")) tableName = tableName.Substring(1, tableName.Length - 2);
                    if (tableName.EndsWith("$"))
                    {
                        string sheet = string.Format(sqlStr, tableName);

                        using (OleDbCommand dbCmd = new OleDbCommand(sheet, dbConn))
                        using (OleDbDataAdapter dbAdapter = new OleDbDataAdapter(dbCmd))
                        using (DataTable dt = new DataTable())
                        {
                            dbAdapter.FillSchema(dt, SchemaType.Source);
                            dbAdapter.Fill(dt);

                            try
                            {
                                int iColCount = dt.Columns.Count;
                                for (int i = 0; i < iColCount; i++)
                                {
                                    if (RegExp(dt.Columns[i].ToString()))
                                    {
                                        complete = true;
                                        break;
                                    }
                                }
                                foreach (DataRow dr in dt.Rows)
                                {
                                    for (int i = 0; i < iColCount; i++)
                                    {
                                        if (!Convert.IsDBNull(dr[i]) && RegExp(dr[i].ToString()))
                                        {
                                            complete = true;
                                            break;
                                        }
                                    }
                                    if (complete) break;
                                }
                            }
                            catch (OleDbException msg)
                            {
                                Debug.WriteLine(msg.Message);
                            }
                        }
                    }
                }
            }
            return complete;
        }

        public bool Analysis(string Path, string excelType)
        {
            bool CompleteConn = false;
            string connPath = (excelType.Equals("xls")) ?
                string.Format(ConnectStrFrm_Excel97_2003, Path) :
                string.Format(ConnectStrFrm_Excel, Path);

            //The connection to that file
            using (OleDbConnection dbConn = new OleDbConnection(connPath))
            {
                try
                {
                    dbConn.Open();
                    CompleteConn = (tableSearching(dbConn)) ? true : false;
                }
                catch (Exception ex)
                {
                    Debug.WriteLine(Path + ": " + ex.Message);
                }
                finally
                {
                    if (dbConn.State != ConnectionState.Closed) dbConn.Close();
                }
            }
            return CompleteConn;
        }

        private bool RegExp(string str)
        {
            foreach (string p in DefaultPattern)
            {
                if (System.Text.RegularExpressions.Regex.IsMatch(str, p))
                {
                    mainform.transData.PassedTypeADD((str.Length == 14) ? InfoType.ResidentNum : InfoType.PhoneNum);
                    return true;
                }
            }
            return false;
        }
    }
}
